------------------
-- if else分支结构
------------------
-- 某用户银行卡号为：6225547854125656，
-- 该用户执行取钱操作，取钱5000元，余额充足则进行取钱操作，
-- 并提示“取钱成功”，否则提示“余额不足”
declare @cardNo varchar(80) ='6225547854125656'

if 5000<(select CardBalance from BankCard where CardNo=@cardNo)
begin
 update  BankCard set CardBalance=CardBalance-5000  where CardNo=@cardNo
 print '取钱成功'
end
else
begin 
print '你好 你的余额不足5000'
end

------------------
-- case when
----------      --------
--	查询银行卡信息，将银行卡状态的1,2,3,4，分别转换成汉字：正常，挂失，冻结，注销
--	并且根据银行卡余额显示银行卡等级：30万以下为一般用户,30万以上为vip用户
--	显示列分别为：卡号、身份证、姓名、余额、用户等级、银行卡状态

select a.CardNo,b.AccountCode,b.RealName,a.CardBalance,
case when CardState = '1'  then '正常'
when CardState = '2'  then '挂失'
when CardState = '3'  then '冻结'
when CardState = '4'  then '注销'
else '不正常'
end as 状态,
case when CardBalance> '300000' then 'vip用户'
else '一般用户'
end as 级别
from BankCard a
inner join AccountInfo b on a.AccountId=b.AccountId


------------------
------------------
    -- 1. 循环打印1-10
    -- 循环结构while
declare @i int =1
	while @i<=10
	begin
	   
		print @i
		 set  @i+=1
	end



    --	2. 循环打印九九乘法表
	declare @x int =1
	while @x<10
	 begin
	 	  
	 declare @y int=1
	 declare @str varchar(180)=''

	  while @y<=@x
	   begin

	    set @str+= cast(@x as char(1))+'x'+cast(@y as char(1))+'='+cast((@x*@y) as char(2))+'   '
		
		 set @y+=1

	   end
	   print @str
	  set @x+=1 

	 end